﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using com.seascape.tools;
using com.seascape.db;
using System.Data;
using System.Text;

/// <summary>
/// 工具类
/// </summary>
public class DataBaseUtility
{
    static List<DbHelper> connList;

    public DataBaseUtility()
    {
        connList = new List<DbHelper>();
    }

    /// <summary>
    /// 获取数据库连接数目
    /// </summary>
    /// <returns></returns>
    public static int GetDatabaseConnctionCount()
    {
        return connList.Count;
    }

    /// <summary>
    /// 关闭所有数据库连接
    /// </summary>
    public static void DisconnectionAllConnection()
    {
        foreach (DbHelper item in connList)
        {
            if (item.GetConnection().State != System.Data.ConnectionState.Closed)
            {
                item.GetConnection().Close();
            }
        }
    }

    /// <summary>
    /// 数据库连接字符串
    /// </summary>
    static String WEATHER_CONN_STR = "WEATHER_CONN_STR";

    /// <summary>
    /// 获取数据库连接
    /// </summary>
    /// <returns></returns>
    static DbHelper GetDatabaseHelper()
    {
        SqlServerHelper helper = new SqlServerHelper(BasicTool.GetConnectionstring(WEATHER_CONN_STR));
        if (connList == null)
        {
            connList = new List<DbHelper>();
        }
        connList.Add(helper);
        return helper;
    }

    /// <summary>
    /// 获取所有省份信息
    /// </summary>
    /// <returns></returns>
    public static List<Province> loadProvince()
    {
        List<Province> pList = new List<Province>();
        using (DataTable dt = GetDatabaseHelper().GetDataTable("select id,provEn,provCn,areaid from area where id in ( select max(id) from area GROUP BY provCn) order by id asc"))
        {
            if (dt != null && dt.Rows.Count > 0)
            {
                foreach (DataRow r in dt.Rows)
                {
                    int id = int.Parse(r["id"].ToString());
                    String areaid = r["areaid"].ToString().Substring(0, 5);
                    String EnName = r["provEn"].ToString();
                    String Name = r["provCn"].ToString();

                    pList.Add(new Province()
                    {
                        Code = areaid, //前5位为省份编码
                        EnName = EnName,
                        Name = Name,
                        Id = id
                    });
                }
            }
        }
        return pList;
    }

    /// <summary>
    /// 加载指定省份对应的所有城市
    /// </summary>
    /// <param name="ProvinceCode"></param>
    /// <returns></returns>
    public static List<City> LoadCity(String pCode)
    {
        List<City> cityList = new List<City>();
        using (DataTable dt = GetDatabaseHelper().GetDataTable("select id,DistrictCn,DistrictEn,areaid from area where areaid like '" + pCode + "%01' order by id asc"))
        {
            if (dt != null && dt.Rows.Count > 0)
            {
                foreach (DataRow r in dt.Rows)
                {
                    cityList.Add(new City()
                    {
                        Code = r["areaid"].ToString().Substring(0, 7), //前7位为市级编码
                        EnName = r["DistrictEn"].ToString(),
                        Name = r["DistrictCn"].ToString(),
                        ProvinceCode = pCode,
                        Id = int.Parse(r["id"].ToString())
                    });
                }
            }
        }
        return cityList;
    }

    public static List<County> LoadCounty(String cCode)
    {
        List<County> countyList = new List<County>();
        using (DataTable dt = GetDatabaseHelper().GetDataTable("select id,NameEn,NameCn,areaid from area where areaid like '" + cCode + "%' order by id asc"))
        {
            if (dt != null && dt.Rows.Count > 0)
            {
                foreach (DataRow r in dt.Rows)
                {
                    countyList.Add(new County()
                    {
                        Code = r["areaid"].ToString(),
                        EnName = r["NameEn"].ToString(),
                        Name = r["NameCn"].ToString(),
                        CityCode = cCode,
                        Id = int.Parse(r["id"].ToString())
                    });
                }
            }
        }
        return countyList;
    }

    public  static String LoadAllCounty()
    {
        StringBuilder sb = new StringBuilder();
        using (DataTable dt = GetDatabaseHelper().GetDataTable("select areaid,NameEn,NameCn,DistrictCn,DistrictEn,provCn,provEn from area order by id asc"))
        {
            if (dt != null && dt.Rows.Count > 0)
            {
                foreach (DataRow r in dt.Rows)
                {
                    sb.Append(String.Format("{0}|{1}|{2}|{3}|{4}|{5}|{6},", r["areaid"], r["NameCn"], r["NameEn"], r["DistrictCn"], r["DistrictEn"], r["provCn"], r["provEn"]));                        
                }
            }
        }
        return sb.ToString();
    }

    /// <summary>
    /// 得到所有的热门城市
    /// </summary>
    /// <returns></returns>
    public static List<County> getAllHotCounty()
    {
        List<County> countyList = new List<County>();
        using (DataTable dt = GetDatabaseHelper().GetDataTable("select id,NameEn,NameCn,areaid from area where isCity=1 order by id asc"))
        {
            if (dt != null && dt.Rows.Count > 0)
            {
                foreach (DataRow r in dt.Rows)
                {
                
                    countyList.Add(new County()
                    {
                        Code = r["areaid"].ToString(),
                        EnName = r["NameEn"].ToString(),
                        Name = r["NameCn"].ToString(),
                        CityCode = r["areaid"].ToString(),
                        Id = int.Parse(r["id"].ToString())
                    });
                }
            }
        }
        return countyList;
    }
    
    /// <summary>
    /// 得到所有的热门城市
    /// </summary>
    /// <returns></returns>
    public static List<County> getUnkownCounty()
    {
        List<County> countyList = new List<County>();
        using (DataTable dt = GetDatabaseHelper().GetDataTable("select * from Area where Areaid not in(select areaid from WeatherInfo)"))
        {
            if (dt != null && dt.Rows.Count > 0)
            {
                foreach (DataRow r in dt.Rows)
                {
                
                    countyList.Add(new County()
                    {
                        Code = r["areaid"].ToString(),
                        EnName = r["NameEn"].ToString(),
                        Name = r["NameCn"].ToString(),
                        CityCode = r["areaid"].ToString(),
                        Id = int.Parse(r["id"].ToString())
                    });
                }
            }
        }
        return countyList;
    }

    public static DataTable ReadTop20NewWeather()
    {
        string sql = "select top 20 areaid,simple,simplepublishtime,details,detailsPublishtime,lastupdateOn from weatherinfo order by lastupdateOn desc";
        using (DataTable dt = GetDatabaseHelper().GetDataTable(sql))
        {
            return dt;
        }
    }

    public static bool UpdateHotCity(string code,bool isHot)
    {
        DbHelper helper = null;
        try
        {
            helper = GetDatabaseHelper();
            helper.ExecuteSqlNoResult("update arae set iscity="+ (isHot?1:0) +" where areaid=" + code);
            return true;
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
            return false;
        }
    }

    public static string ReadWeather(string code, int type)
    {

        string sql = "select simple,simplepublishtime from weatherinfo where areaid=" + code;
        if (type == FormatUtility.DETAILS)
        {
            sql = "select details,detailsPublishtime from weatherinfo where areaid=" + code; ;
        }
        using (DataTable dt = GetDatabaseHelper().GetDataTable(sql))
        {
            if (dt.Rows.Count > 0)
            {
                return dt.Rows[0][1].ToString() + "|" + dt.Rows[0][0].ToString();
            }
            else
            {
                return "";
            }
        }
    }

    public static void SaveWeather(string result, string code, int type)
    {
        DbHelper helper = null;
        try
        {
            helper = GetDatabaseHelper();
            object o = helper.GetOne("select count(id) from weatherInfo where areaid=" + code);
            if (o != null && Convert.ToInt16(o) > 0)
            {
                if (type == FormatUtility.SIMPLE)
                {
                    LitJson.JsonData weatherInfo = LitJson.JsonMapper.ToObject(result)["weatherinfo"];
                    DateTime sDate = FormatUtility.FormatDateFromWeather(weatherInfo["time"].ToString());
                    helper.ExecuteSqlNoResult("update weatherInfo set simple='" + result + "',simplePublishTime='" + sDate.ToString("yyyy-MM-dd HH:mm:ss") + "',lastUpdateOn=getdate() where areaid=" + code);
                }
                else if (type == FormatUtility.DETAILS)
                {
                    LitJson.JsonData weatherInfo = LitJson.JsonMapper.ToObject(result)["weatherinfo"];
                    DateTime dDate = FormatUtility.FormatDateFromWeather(weatherInfo["fchh"].ToString() + ":00");
                    helper.ExecuteSqlNoResult("update weatherInfo set details='" + result + "',detailsPublishTime='" + dDate.ToString("yyyy-MM-dd HH:mm:ss") + "',lastUpdateOn=getdate() where areaid=" + code);
                }
            }
            else
            {
                string simple = "", details = "";
                DateTime sDate = DateTime.Now.AddHours(-1), dDate = DateTime.Now.AddHours(-1);
                if (type == FormatUtility.SIMPLE)
                {
                    simple = result;
                    LitJson.JsonData weatherInfo = LitJson.JsonMapper.ToObject(simple)["weatherinfo"];
                    sDate = FormatUtility.FormatDateFromWeather(weatherInfo["time"].ToString());
                }
                if (type == FormatUtility.DETAILS)
                {
                    details = result;
                    LitJson.JsonData weatherInfo = LitJson.JsonMapper.ToObject(details)["weatherinfo"];
                    sDate = FormatUtility.FormatDateFromWeather(weatherInfo["fchh"].ToString() + ":00");
                }
                helper.ExecuteSqlNoResult("insert into weatherInfo (areaid,simple,simplePublishTime,details,detailsPublishTime,lastUpdateOn) values ('" + code + "','" + simple + "','" + sDate.ToString("yyyy-MM-dd HH:mm:ss") + "','" + details + "','" + dDate.ToString("yyyy-MM-dd HH:mm:ss") + "',getdate())");
            }


        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            if (helper != null)
            {
                helper.GetConnection().Close();
            }
            helper = null;
        }
    }

}